<!DOCTYPE html>
<html>
<head>
    <meta name="generator" content="Hugo 0.92.2" />

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="description" content="My thoughts and rambles">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link rel="icon" type="image/png" href="http://example.org//images/favicon.ico">

    
    <meta name="mobile-web-app-capable" content="yes">
    <link rel="icon" sizes="192x192" href="http://example.org/images/touch/chrome-touch-icon-192x192.png">

    
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="apple-mobile-web-app-title" content="Material Design Lite">
    <link rel="apple-touch-icon-precomposed" href="apple-touch-icon-precomposed.png">

    
    <meta name="msapplication-TileImage" content="http://example.org/images/touch/ms-touch-icon-144x144-precomposed.png">
    <meta name="msapplication-TileColor" content="#3372DF">

    <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:regular,bold,italic,thin,light,bolditalic,black,medium&amp;lang=en"/>
    <link rel="stylesheet" href="http://example.org/css/ionicons.min.css"/>
    <link rel="stylesheet" href="https://storage.googleapis.com/code.getmdl.io/1.1.3/material.grey-orange.min.css"/>
    <link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
    <link rel="stylesheet" href="http://example.org/css/hmdl-style.css"/>



    <title>aaa</title>
</head>

<body style="background-image: url('/');">
    <div class="hmdl-body mdl-layout mdl-js-layout has-drawer is-upgraded">        
        <header class="mdl-layout__header mdl-layout__header--transparent mdl-layout__header--scroll">
            <div class="mdl-layout__header-row">
                <div class="mdl-layout-spacer"></div>
                <nav class="mdl-navigation">
                </nav>
            </div>
        </header>
        <div class="mdl-layout__drawer">
            <nav class="mdl-navigation">
            </nav>
        </div>

        <main class="mdl-layout__content">

            <div class="hmdl-page mdl-grid">
                <div class="mdl-card mdl-shadow--4dp mdl-cell mdl-cell--12-col">
                    <div class="hmdl-page-banner mdl-card__media mdl-color-text--grey-50" style=" ">
                        <h3 >Aaa</h3>
                    </div>
                    <div class="hmdl-page-meta mdl-color-text--grey-700 mdl-card__supporting-text">
                        <div>
                            <strong></strong>
                            <span>Feb 16, 2022</span>
                        </div>
                        <div class="section-spacer"></div>
                    </div>
                    <div class="hmdl-page-content mdl-color-text--grey-700 mdl-card__supporting-text">
                        <h1 id="mysql-mgr集群部署">MySQL MGR集群部署</h1>
<p>[TOC]</p>
<h2 id="前提条件">前提条件</h2>
<ul>
<li>
<p>MySQL版本需要大于等于5.7.17</p>
</li>
<li>
<p>引擎必须为InnoDB</p>
</li>
<li>
<p>表必须有主键</p>
</li>
<li>
<p>必须开启Binlog且为row格式</p>
</li>
<li>
<p>必须打开GTID特性，且主从状态信息存于表中</p>
</li>
<li>
<p><a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html">Group Replication Requirements</a></p>
</li>
<li>
<p><a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication-limitations.html">Group Replication Limitations</a></p>
</li>
<li>
<p><a href="https://www.cnblogs.com/zmc60/p/15180098.html">Mysql之MGR的限制和局限性</a></p>
</li>
</ul>
<h2 id="节点信息">节点信息</h2>
<h3 id="主机信息">主机信息</h3>
<ul>
<li>hdspnew004 - 172.22.3.117</li>
<li>hdspnew005 - 172.22.3.118</li>
<li>hdspnew006 - 172.22.3.119</li>
</ul>
<h3 id="目录信息">目录信息</h3>
<ul>
<li>应用目录：<code>/opt/mysql</code></li>
<li>数据目录：<code>/data/mysql</code></li>
</ul>
<h3 id="节点配置">节点配置</h3>
<ul>
<li>配置主机名和IP地址</li>
<li>配置HOSTS</li>
<li>关闭防火墙</li>
<li>关闭SELinux</li>
<li>开启时间同步</li>
<li>配置免密登录</li>
</ul>
<h2 id="安装mysql">安装MySQL</h2>
<ul>
<li>
<p>安装必要依赖：<code>yum -y install perl perl-devel autoconf libaio numactl libnuma.so.1</code></p>
</li>
<li>
<p>进入应用目录：<code>cd /opt</code></p>
</li>
<li>
<p>下载MySQL（<a href="https://downloads.mysql.com/archives/community/">下载地址</a>在此，版本自行选择）：<code>wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz</code></p>
</li>
<li>
<p>解压缩：<code>tar -zxf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz</code></p>
</li>
<li>
<p>重命名：<code>mv mysql-5.7.34-linux-glibc2.12-x86_64 mysql-5.7.34</code></p>
</li>
<li>
<p>创建用户组及用户</p>
<ul>
<li>创建组：<code>groupadd mysql</code></li>
<li>创建用户：<code>useradd -r -g mysql mysql</code></li>
<li>为用户设置密码：<code>passwd mysql</code>（密码暂设为mysql，根据实际情况修改）</li>
</ul>
</li>
<li>
<p>为mysql用户赋权：<code>chown -R mysql:mysql /opt/mysql-5.7.34</code>、<code>chown -R mysql:mysql /data/mysql/</code></p>
</li>
<li>
<p>备份配置文件：<code>cp /etc/my.cnf /etc/my.cnf.bak</code></p>
</li>
<li>
<p>编辑配置文件：<code>vim /etc/my.cnf</code></p>
<blockquote>
<p>配置项中这三处不同：server_id、loose-group_replication_local_address、report_host</p>
</blockquote>
<ul>
<li>
<p>hdspnew004</p>
<pre tabindex="0"><code>[mysqld]

# 基础设置
bind-address=0.0.0.0
port=23306
user=root
basedir=/opt/mysql-5.7.34/
datadir=/data/mysql/
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
lower_case_table_names=1
max_connections=1000

# 组复制限制
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64

slave_preserve_commit_order=1
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=1

# 组复制设置
group_replication_group_name=&quot;05153196-c2ab-43ae-b355-b9832eacf0b1&quot;
group_replication_start_on_boot=off
group_replication_local_address=&quot;hdspnew004.hand-china.com:23307&quot;
group_replication_group_seeds=&quot;hdspnew004.hand-china.com:23307,hdspnew005.hand-china.com:23307,hdspnew006.hand-china.com:23307&quot;
group_replication_bootstrap_group=off

report_host=hdspnew004.hand-china.com
report_port=23306
</code></pre></li>
<li>
<p>hdspnew005</p>
<pre tabindex="0"><code>[mysqld]

# 基础设置
bind-address=0.0.0.0
port=23306
user=root
basedir=/opt/mysql-5.7.34/
datadir=/data/mysql/
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
lower_case_table_names=1
max_connections=1000

# 组复制限制
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64

slave_preserve_commit_order=1
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=1

# 组复制设置
group_replication_group_name=&quot;05153196-c2ab-43ae-b355-b9832eacf0b1&quot;
group_replication_start_on_boot=off
group_replication_local_address=&quot;hdspnew005.hand-china.com:23307&quot;
group_replication_group_seeds=&quot;hdspnew004.hand-china.com:23307,hdspnew005.hand-china.com:23307,hdspnew006.hand-china.com:23307&quot;
group_replication_bootstrap_group=off

report_host=hdspnew005.hand-china.com
report_port=23306
</code></pre></li>
<li>
<p>hdspnew006</p>
<pre tabindex="0"><code>[mysqld]

# 基础设置
bind-address=0.0.0.0
port=23306
user=root
basedir=/opt/mysql-5.7.34/
datadir=/data/mysql/
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
lower_case_table_names=1
max_connections=1000

# 组复制限制
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64

slave_preserve_commit_order=1
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=1

# 组复制设置
group_replication_group_name=&quot;05153196-c2ab-43ae-b355-b9832eacf0b1&quot;
group_replication_start_on_boot=off
group_replication_local_address=&quot;hdspnew006.hand-china.com:23307&quot;
group_replication_group_seeds=&quot;hdspnew004.hand-china.com:23307,hdspnew005.hand-china.com:23307,hdspnew006.hand-china.com:23307&quot;
group_replication_bootstrap_group=off

report_host=hdspnew006.hand-china.com
report_port=23306
</code></pre></li>
</ul>
</li>
<li>
<p>进入mysql bin目录：<code>cd /opt/mysql-5.7.34/bin/</code></p>
</li>
<li>
<p>初始化：<code>./mysqld --defaults-file=/etc/my.cnf --basedir=/opt/mysql-5.7.34/  --datadir=/data/mysql/ --user=mysql --initialize</code></p>
</li>
<li>
<p>注册MySQL服务：<code>cp /opt/mysql-5.7.34/support-files/mysql.server /etc/init.d/mysql</code></p>
</li>
<li>
<p>启动MySQL：<code>service mysql start</code></p>
</li>
<li>
<p>创建软连接：<code>ln -s /opt/mysql-5.7.34/bin/mysql /usr/bin</code></p>
</li>
<li>
<p>查看初始化密码：<code>cat /data/mysql/mysql.err | awk -F : '/root@localhost/{print $4}'</code></p>
</li>
<li>
<p>登录MySQL：<code>mysql -uroot -p</code></p>
</li>
<li>
<p>设置新密码：<code>SET PASSWORD = PASSWORD('peaUFnY4RMTK2dLYwPDZ3AYD');</code></p>
</li>
<li>
<p>设置密码不过期：<code>ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;</code></p>
</li>
<li>
<p>开启远程登录</p>
<ul>
<li><code>use mysql; </code></li>
<li><code>update user set host = '%' where user = 'root';</code></li>
</ul>
</li>
<li>
<p>刷新权限：<code>FLUSH PRIVILEGES; </code></p>
</li>
<li>
<p>本地连接测试下</p>
</li>
</ul>
<h2 id="配置mgr">配置MGR</h2>
<blockquote>
<p>所有节点</p>
</blockquote>
<ul>
<li>登录MySQL：<code>mysql -uroot -p</code></li>
<li>加载MGR插件：<code>INSTALL PLUGIN group_replication SONAME 'group_replication.so';</code></li>
<li>设置复制账号（创建账号不记录到binlog中）
<ul>
<li><code>SET SQL_LOG_BIN=0;</code></li>
<li><code>CREATE USER repl@'%' IDENTIFIED BY 'hdsp_new123$%^';</code></li>
<li><code>GRANT REPLICATION SLAVE ON *.* TO repl@'%';</code></li>
<li><code>FLUSH PRIVILEGES;</code></li>
<li><code>SET SQL_LOG_BIN=1;</code></li>
<li><code>CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='hdsp_new123$%^' FOR CHANNEL 'group_replication_recovery';</code>（我直接使用的root用户同步的，也可以设置新创建的repl用户进行同步）</li>
</ul>
</li>
</ul>
<h2 id="单主模式">单主模式</h2>
<ul>
<li>
<p>在主库上执行（hdspnew004）</p>
<ul>
<li>
<p><code>SET GLOBAL group_replication_bootstrap_group=ON;</code></p>
</li>
<li>
<p><code>START GROUP_REPLICATION;</code></p>
</li>
<li>
<p><code>SET GLOBAL group_replication_bootstrap_group=OFF;</code></p>
</li>
<li>
<p>查看MGR信息：<code>SELECT * FROM performance_schema.replication_group_members;</code></p>
<pre tabindex="0"><code>mysql&gt; SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bdd6491c-0d00-11ec-897f-022f62428036 | hdspnew004  |       23306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
</code></pre></li>
</ul>
</li>
<li>
<p>在从库上执行（hdspnew005，hdspnew006）</p>
<ul>
<li>加入MGR：<code>START GROUP_REPLICATION;</code></li>
</ul>
</li>
<li>
<p>在主库上执行（hdspnew004）</p>
<ul>
<li>
<p>查看MGR信息：<code>SELECT * FROM performance_schema.replication_group_members;</code></p>
<pre tabindex="0"><code>mysql&gt; SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | b4c7f15b-1ae9-11ec-b14d-2e883d6073f2 | hdspnew006  |       23306 | ONLINE       |
| group_replication_applier | b701b6d0-1ae9-11ec-99b6-4efc25056ea1 | hdspnew005  |       23306 | ONLINE       |
| group_replication_applier | bdd6491c-0d00-11ec-897f-022f62428036 | hdspnew004  |       23306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.01 sec)
</code></pre></li>
</ul>
</li>
<li>
<p>测试下：主节点新建个数据库，从库会自动同步到数据库</p>
</li>
</ul>
<h2 id="多主模式">多主模式</h2>
<blockquote>
<p>MRG切换模式需要重新启动组复制，因此需要在所有节点先关闭组复制</p>
</blockquote>
<ul>
<li>
<p>停止组复制（所有节点）</p>
<pre tabindex="0"><code>stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
</code></pre></li>
<li>
<p>hdspnew004节点执行</p>
<pre tabindex="0"><code>SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
</code></pre></li>
<li>
<p>hdspnew005，hdspnew006节点执行</p>
<pre tabindex="0"><code>START GROUP_REPLICATION; 
</code></pre></li>
<li>
<p>查看下组信息：hdspnew004节点执行</p>
<pre tabindex="0"><code>mysql&gt; SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | b4c7f15b-1ae9-11ec-b14d-2e883d6073f2 | hdspnew006  |       23306 | ONLINE       |
| group_replication_applier | b701b6d0-1ae9-11ec-99b6-4efc25056ea1 | hdspnew005  |       23306 | ONLINE       |
| group_replication_applier | bdd6491c-0d00-11ec-897f-022f62428036 | hdspnew004  |       23306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
</code></pre></li>
<li>
<p>测试下：任意节点新建数据库，其他节点会自动同步到各自的数据库</p>
</li>
</ul>
<h2 id="连接信息">连接信息</h2>
<ul>
<li>JDBC URL：jdbc:mysql://hdspnew004:23306,hdspnew005:23306,hdspnew006:23306/test?useSSL=false&amp;autoReconnect=true</li>
<li>hdspnew004 - 172.22.3.117:23306 - root/hdsp_new123$%^</li>
<li>hdspnew005 - 172.22.3.118:23306 - root/hdsp_new123$%^</li>
<li>hdspnew006 - 172.22.3.119:23306 - root/hdsp_new123$%^</li>
</ul>
<h2 id="问题整理">问题整理</h2>
<ul>
<li>
<p>新加入节点状态为RECOVERING，且错误日志（mysql.err）中出现如下信息</p>
<pre tabindex="0"><code>2021-09-22T02:30:35.095502Z 61 [ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*EE5CC2A2157422DAE86C775198C0F83FB4C59465'', Error_code: 1396
2021-09-22T02:30:35.095524Z 61 [Warning] Slave: Operation ALTER USER failed for 'root'@'localhost' Error_code: 1396
2021-09-22T02:30:35.095557Z 61 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with &quot;SLAVE START&quot;. We stopped at log 'binlog.000002' position 150.
</code></pre><ul>
<li>解决方法
<ul>
<li>停止组复制：<code>STOP GROUP_REPLICATION;</code></li>
<li>主节点执行：<code>reset master;</code></li>
</ul>
</li>
</ul>
</li>
<li>
<p>3092 - The server is not configured properly to be an active member of the group. Please see more details on error log.</p>
<pre tabindex="0"><code>2021-09-28T03:50:17.924611Z 8 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 1 failed executing transaction '05153196-c2ab-43ae-b355-b9832eacf0b1:1705'; Could not execute Delete_rows event on table scm.processes_detail; Can't find record in 'processes_detail', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND, Error_code: 1032
2021-09-28T03:50:17.924745Z 7 [Warning] Slave SQL for channel 'group_replication_applier': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2021-09-28T03:50:17.924774Z 7 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2021-09-28T03:50:17.924804Z 7 [ERROR] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'
2021-09-28T03:50:17.924913Z 7 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 0
2021-09-28T03:50:17.925029Z 4 [ERROR] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2021-09-28T03:50:17.925078Z 4 [ERROR] Plugin group_replication reported: '[GCS] The member is already leaving or joining a group.'
2021-09-28T03:50:17.925107Z 4 [ERROR] Plugin group_replication reported: 'Unable to confirm whether the server has left the group or not. Check performance_schema.replication_group_members to check group membership information.'
2021-09-28T03:50:17.925121Z 4 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2021-09-28T03:50:17.959068Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2021-09-28T03:50:17.959109Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 23307'
2021-09-28T03:50:22.700769Z 0 [ERROR] Plugin group_replication reported: 'There was a previous plugin error while the member joined the group. The member will now exit the group.'
2021-09-28T03:50:22.700860Z 0 [ERROR] Plugin group_replication reported: 'Message received while the plugin is not ready, message discarded'
</code></pre><ul>
<li>解决方法
<ul>
<li>执行语句：<code>reset master</code></li>
</ul>
</li>
</ul>
</li>
<li>
<p>其他问题可以参考这里：</p>
<ul>
<li><a href="https://blog.csdn.net/lijingkuan/article/details/80827779">https://blog.csdn.net/lijingkuan/article/details/80827779</a></li>
<li><a href="https://cloud.tencent.com/developer/article/1533657">MGR搭建过程中遇到的错误以及解决办法</a></li>
</ul>
</li>
</ul>
<h2 id="参考文档">参考文档</h2>
<ul>
<li><a href="https://www.cnblogs.com/nothingonyou/p/12145348.html">mysql 高可用之MGR</a></li>
<li><a href="https://www.jianshu.com/p/ca1af156f656">MySQL MGR 集群搭建（单主模式&amp;多主模式）</a></li>
</ul>
<h2 id="其他">其他</h2>
<pre tabindex="0"><code>set global read_only=0; 
</code></pre><pre tabindex="0"><code>set global group_replication_allow_local_disjoint_gtids_join=ON;
</code></pre><pre tabindex="0"><code>STOP GROUP_REPLICATION;
</code></pre><pre tabindex="0"><code>START GROUP_REPLICATION;
</code></pre>
                    </div>
                    <div class="hmdl-page-comments mdl-color-text--primary-contrast mdl-card__supporting-text comments"> 
                        <a href=></a>
                        <p></p>
                    </div>  
                </div>                
                <nav class="mdl-color-text--grey-50 mdl-cell mdl-cell--12-col">
                    <div class="section-spacer"></div>
                    <a href="/post/first/">
                        Newer
                        <button class="mdl-button mdl-js-button mdl-js-ripple-effect mdl-button--icon mdl-color--white mdl-color-text--grey-900" role="presentation">
                            <i class="icon ion-android-arrow-forward"></i>
                        </button>
                    </a>
                </nav>
 
            </div>        
        </main>
        <footer class="mdl-mini-footer">
            <div class="mdl-mini-footer--left-section">                

            </div>
            <div class="mdl-mini-footer--right-section">
                <span>©  </span>
            </div>
        </footer>
        <div class="mdl-layout__obfuscator"></div>
    </div>
    <script src="https://code.getmdl.io/1.1.3/material.min.js"></script>


</body>
</html>

